Introduction to Java Database Connectivity (JDBC)

Course: Advanced Java Training

Topic: Introduction to Java Database Connectivity (JDBC)

Course Link: Advanced Java Training Course

Demo Link: Register for a Course Demo

Website: JustAcademy Homepage

1. Introduction to JDBC

Java Database Connectivity (JDBC) is a standard Java API that allows Java applications to connect to relational databases, execute SQL statements, retrieve records, modify data, and manage database transactions.

Applications often need to store information permanently rather than keeping it only in memory. For example, a student management system may store student names, email addresses, course details, and examination results in a database. JDBC provides the programming interfaces needed to access this information from Java code.

JDBC provides a common API, while a database-specific JDBC driver handles communication with the selected database. Common database systems used with JDBC include MySQL, PostgreSQL, Oracle Database, and Microsoft SQL Server.

2. Learning Objectives

• Understand JDBC and its importance in Java development.
• Learn JDBC architecture and the role of database drivers.
• Understand important JDBC interfaces and classes.
• Connect a Java application to a relational database.
• Execute SQL queries using Statement and PreparedStatement.
• Perform CRUD operations using JDBC.
• Read query results using ResultSet.
• Handle database exceptions and manage resources.
• Understand transactions, batch operations, and connection pooling.
• Apply secure and efficient database programming practices.

3. Why Do We Need JDBC?

Java applications frequently interact with databases to store, retrieve, update, and delete business information. JDBC provides a standard mechanism for carrying out these operations from Java programs.

Major Uses of JDBC

• Database connectivity: Establishes a connection between Java code and a database.
• Data retrieval: Retrieves records using SQL SELECT queries.
• Data insertion: Stores new records in database tables.
• Data modification: Updates existing records.
• Data deletion: Removes records according to specified conditions.
• Transaction management: Commits or rolls back related database operations.
• Enterprise applications: Supports database access in Java web applications and business systems.

4. JDBC Architecture

JDBC architecture describes how a Java application communicates with a database using the JDBC API and a compatible database driver.

Java Application
       |
       v
JDBC API
       |
       v
JDBC Driver
       |
       v
Database Server
       |
       v
Database Tables and Records

Architecture Components

1. Java Application: Contains the business logic that requires database access.
2. JDBC API: Provides standard interfaces and classes for database operations.
3. JDBC Driver: Implements the communication protocol required by a particular database.
4. Database Server: Executes SQL statements and manages stored information.
5. Result Processing: The application processes returned rows or update counts.

5. JDBC Packages

The java.sql package is used in most introductory JDBC programs. The javax.sql package is commonly used in enterprise applications that use data sources and connection pooling.

6. Types of JDBC Drivers

For new applications, a compatible Type 4 driver is generally the usual choice. Examples include MySQL Connector/J and the PostgreSQL JDBC driver.

7. Important JDBC Interfaces and Classes

8. Prerequisites for JDBC

To begin JDBC development, prepare the following components:

• A compatible Java Development Kit (JDK).
• A relational database such as MySQL or PostgreSQL.
• A database schema and tables.
• A JDBC driver compatible with the selected database.
• A Java IDE or build tool such as Eclipse, IntelliJ IDEA, Maven, or Gradle.
• Database credentials with the necessary permissions.

9. Setting Up a MySQL Database

The following examples use MySQL. The SQL syntax and connection details may need adjustment for other database systems.

Step 1: Create a Database

CREATE DATABASE college_db;

Step 2: Select the Database

USE college_db;

Step 3: Create a Table

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INT
);

Step 4: Insert Sample Records

INSERT INTO students (name, email, age)
VALUES
('Aarav Sharma', '[email protected]', 21),
('Meera Patel', '[email protected]', 22),
('Rohan Verma', '[email protected]', 20);

10. Adding the JDBC Driver

Java applications need a suitable JDBC driver to communicate with the selected database. For MySQL, the driver is called MySQL Connector/J.

MySQL Connector/J Official Documentation

Maven Dependency Example

Add the MySQL Connector/J dependency to the project's pom.xml. Replace the placeholder version with a compatible release.

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>YOUR_COMPATIBLE_VERSION</version>
</dependency>

If Maven is not used, add the appropriate JDBC driver JAR to the application's classpath.

11. Establishing a Database Connection

The DriverManager.getConnection() method obtains a database connection using the JDBC URL, username, and password.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JdbcConnectionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/college_db";
        String username = "app_user";
        String password = System.getenv("DB_PASSWORD");

        try (Connection con =
                 DriverManager.getConnection(url, username, password)) {

            System.out.println("Database connection successful.");

        } catch (SQLException e) {
            System.err.println("Database connection failed.");
            e.printStackTrace();
        }
    }
}

Explanation

• The JDBC URL identifies the database host, port, and database name.
• The username and password authenticate the database connection.
• The Connection object represents the active connection.
• Try-with-resources automatically closes the connection after use.
• The password is read from an environment variable instead of being hard-coded.

Modern JDBC drivers commonly register themselves automatically when available on the application's classpath. Explicit Class.forName() calls are usually unnecessary for current drivers.

12. Understanding JDBC URLs

A JDBC URL specifies the database system and connection details. The exact format depends on the driver.

These are illustrative examples. Consult the selected driver's documentation for the correct URL syntax and supported connection properties.

13. Executing SQL Using Statement

The Statement interface is used to execute SQL statements that do not require bound parameters.

import java.sql.*;

public class StudentListExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/college_db";
        String username = "app_user";
        String password = System.getenv("DB_PASSWORD");
        String sql = "SELECT id, name, email, age FROM students";

        try (Connection con =
                 DriverManager.getConnection(url, username, password);
             Statement stmt = con.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                System.out.println(
                    rs.getInt("id") + " | " +
                    rs.getString("name") + " | " +
                    rs.getString("email") + " | " +
                    rs.getInt("age")
                );
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Important Statement Methods

• executeQuery() executes a query that returns a result set.
• executeUpdate() executes an INSERT, UPDATE, DELETE, or suitable DDL statement.
• execute() executes SQL when the type of result may vary.
• close() releases statement resources.

14. Using PreparedStatement

PreparedStatement executes parameterized SQL. It uses question marks (?) as placeholders for values. Bound parameters help prevent SQL injection and make it easier to execute the same SQL with different values.

String sql =
    "SELECT id, name, email FROM students WHERE id = ?";

try (Connection con =
         DriverManager.getConnection(url, username, password);
     PreparedStatement ps = con.prepareStatement(sql)) {

    ps.setInt(1, 1);

    try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            System.out.println(rs.getString("name"));
            System.out.println(rs.getString("email"));
        } else {
            System.out.println("Student not found.");
        }
    }
}

Common Parameter Methods

15. CRUD Operations Using JDBC

CRUD stands for Create, Read, Update, and Delete. These are the four fundamental operations used in most database-driven applications.

15.1 Create: Insert a Record

String sql =
    "INSERT INTO students (name, email, age) VALUES (?, ?, ?)";

try (PreparedStatement ps = con.prepareStatement(sql)) {
    ps.setString(1, "Ananya Rao");
    ps.setString(2, "[email protected]");
    ps.setInt(3, 21);

    int rows = ps.executeUpdate();
    System.out.println("Rows inserted: " + rows);
}

15.2 Read: Retrieve Records

String sql = "SELECT id, name, email FROM students";

try (PreparedStatement ps = con.prepareStatement(sql);
     ResultSet rs = ps.executeQuery()) {

    while (rs.next()) {
        System.out.println(
            rs.getInt("id") + " - " +
            rs.getString("name") + " - " +
            rs.getString("email")
        );
    }
}

15.3 Update: Modify a Record

String sql = "UPDATE students SET email = ? WHERE id = ?";

try (PreparedStatement ps = con.prepareStatement(sql)) {
    ps.setString(1, "[email protected]");
    ps.setInt(2, 1);

    int rows = ps.executeUpdate();
    System.out.println("Rows updated: " + rows);
}

15.4 Delete: Remove a Record

String sql = "DELETE FROM students WHERE id = ?";

try (PreparedStatement ps = con.prepareStatement(sql)) {
    ps.setInt(1, 1);

    int rows = ps.executeUpdate();
    System.out.println("Rows deleted: " + rows);
}

Always check the WHERE condition before performing an UPDATE or DELETE operation. Without a suitable condition, the operation may affect every record in the table.

16. Understanding ResultSet

A ResultSet represents tabular data returned by a database query. Its cursor initially sits before the first row. The next() method moves the cursor to the next row and returns true when a row is available.

while (rs.next()) {
    int id = rs.getInt("id");
    String name = rs.getString("name");
    int age = rs.getInt("age");

    System.out.println(id + ": " + name + ", " + age);
}

Common methods include getInt(), getString(), getDouble(), getDate(), and wasNull(). For nullable numeric columns, remember that a getter such as getInt() returns zero for SQL NULL; use wasNull() after reading the column when necessary.

17. CallableStatement and Stored Procedures

CallableStatement is used to invoke stored procedures supported by the database.

Example MySQL Stored Procedure

DELIMITER //

CREATE PROCEDURE GetStudentById(IN p_id INT)
BEGIN
    SELECT id, name, email, age
    FROM students
    WHERE id = p_id;
END //

DELIMITER ;

Calling the Procedure

try (Connection con =
         DriverManager.getConnection(url, username, password);
     CallableStatement cs =
         con.prepareCall("{call GetStudentById(?)}")) {

    cs.setInt(1, 2);

    try (ResultSet rs = cs.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
    }
}

Stored procedure syntax and behavior can vary across database systems.

18. Exception Handling in JDBC

JDBC operations can fail because of invalid SQL, incorrect credentials, missing tables, network problems, or constraint violations. Such errors are commonly reported through SQLException.

• getMessage() returns the exception message.
• getSQLState() returns the SQL state when available.
• getErrorCode() returns a database-specific error code when provided.
• getNextException() accesses the next chained SQL exception.

try {
    // Perform JDBC operations.
} catch (SQLException e) {
    System.err.println("Database operation failed.");
    System.err.println("SQL State: " + e.getSQLState());
    System.err.println("Error Code: " + e.getErrorCode());
    e.printStackTrace();
}

In production applications, use a logging framework and avoid displaying database internals or sensitive information to end users.

19. Closing JDBC Resources

Connections, statements, and result sets consume resources. Leaving them open can cause connection exhaustion and application performance problems.

Use try-with-resources whenever possible:

try (Connection con =
         DriverManager.getConnection(url, username, password);
     PreparedStatement ps =
         con.prepareStatement("SELECT id, name FROM students");
     ResultSet rs = ps.executeQuery()) {

    while (rs.next()) {
        System.out.println(rs.getString("name"));
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Try-with-resources closes the declared resources automatically, even if an exception occurs. In a pooled environment, closing a borrowed connection normally returns it to the pool.

20. JDBC Transactions

A transaction is a group of database operations treated as one logical unit. Transactions help ensure that related changes succeed or fail together.

For example, transferring money between two accounts requires both a debit and a credit. If one operation fails, the application should roll back the transaction to avoid inconsistent data.

Transaction Methods

• setAutoCommit(false) disables automatic commit so the application can control the transaction.
• commit() commits the current transaction.
• rollback() rolls back the current transaction.
• setSavepoint() creates a savepoint when supported.

try {
    con.setAutoCommit(false);

    // Execute related INSERT, UPDATE, or DELETE operations.

    con.commit();
} catch (SQLException e) {
    try {
        con.rollback();
    } catch (SQLException rollbackError) {
        e.addSuppressed(rollbackError);
    }
    throw e;
}

This is a simplified pattern. Real applications should also restore the original connection settings appropriately and account for connection ownership, concurrency, and database-specific transaction behavior.

21. Batch Processing

Batch processing allows multiple SQL operations to be submitted together. It can reduce repeated communication overhead when processing many records.

String sql =
    "INSERT INTO students (name, email, age) VALUES (?, ?, ?)";

try (PreparedStatement ps = con.prepareStatement(sql)) {
    ps.setString(1, "Kabir Shah");
    ps.setString(2, "[email protected]");
    ps.setInt(3, 22);
    ps.addBatch();

    ps.setString(1, "Diya Nair");
    ps.setString(2, "[email protected]");
    ps.setInt(3, 20);
    ps.addBatch();

    int[] results = ps.executeBatch();
    System.out.println("Batch operations: " + results.length);
}

Batch behavior depends on the JDBC driver. Consider using transactions and appropriate error handling when batch changes must remain consistent.

22. Connection Pooling and DataSource

Creating a physical database connection for every request can be expensive. Connection pooling allows applications to reuse connections rather than repeatedly creating new ones.

The DataSource interface provides a standard way to obtain connections and is commonly integrated with connection pools in enterprise applications.

Benefits of Connection Pooling

• Reduces the overhead of repeatedly opening physical connections.
• Can improve performance under suitable workloads.
• Allows configuration of pool size and connection timeouts.
• Helps control the number of concurrent database connections.
• Supports connection reuse and monitoring through the pool implementation.

23. JDBC Security Best Practices

• Use PreparedStatement with bound values for user-supplied data.
• Avoid concatenating untrusted values directly into SQL statements.
• Use a dedicated database account with limited permissions.
• Store credentials in environment variables or a secrets-management system.
• Use encrypted database connections where required.
• Validate user input and apply business rules.
• Allow-list dynamic table or column names because SQL identifiers generally cannot be bound as ordinary parameter values.
• Use transactions for logically related updates.
• Close database resources reliably.
• Keep drivers and database software appropriately updated.
• Avoid logging passwords, tokens, or unnecessary sensitive data.

24. Common JDBC Errors and Solutions

25. Mini Project: Student Management System

Objective: Create a console-based Java application that manages student records using JDBC and MySQL.

Features

• Add a student record.
• Display all students.
• Search by student ID.
• Update student information.
• Delete a student record.
• Handle invalid input and database exceptions.

Suggested Project Structure

StudentManagement/
├── src/main/java/
│   └── com/example/
│       ├── DBConnection.java
│       ├── Student.java
│       ├── StudentDAO.java
│       └── Main.java
└── pom.xml

Development Steps

1. Install Java, MySQL, and a compatible JDBC driver.
2. Create the database and students table.
3. Configure credentials outside the source code.
4. Create a Student model class.
5. Implement CRUD methods in StudentDAO using PreparedStatement.
6. Build a menu-driven application in Main.
7. Test valid input, missing records, duplicate emails, and connection failures.
8. Document the project setup and execution instructions.

26. JDBC Interview Questions and Answers

Q1. What is JDBC?

JDBC is a standard Java API used to connect to databases, execute SQL, retrieve results, and manage database operations.

Q2. What is a JDBC driver?

A JDBC driver implements database-specific communication so a Java application can interact with the selected database.

Q3. What is the difference between Statement and PreparedStatement?

Statement executes SQL strings directly. PreparedStatement supports parameterized SQL and is generally preferred when values come from user input.

Q4. What is ResultSet?

ResultSet represents rows returned by a database query and allows the application to read column values.

Q5. What is DriverManager?

DriverManager locates a suitable registered driver and provides methods for obtaining database connections.

Q6. What is the difference between executeQuery() and executeUpdate()?

executeQuery() is used for queries that return a result set. executeUpdate() is typically used for INSERT, UPDATE, DELETE, and suitable DDL statements.

Q7. What is SQL injection?

SQL injection is a vulnerability in which untrusted input changes the meaning of an SQL statement. Parameterized queries help prevent this class of attack.

Q8. What is a JDBC transaction?

A transaction groups database operations into a unit that can be committed or rolled back.

Q9. What is connection pooling?

Connection pooling reuses database connections to reduce the overhead of repeatedly establishing physical connections.

Q10. What is CallableStatement?

CallableStatement is used to invoke stored procedures supported by a database.

Q11. What is DataSource?

DataSource is an interface for obtaining database connections and is commonly used with connection pools.

Q12. Why should JDBC resources be closed?

Closing resources prevents resource leaks and helps preserve database connection capacity.

27. Practical Exercises

1. Create a Java program that connects to MySQL.
2. Display all rows from the students table.
3. Insert a record using PreparedStatement.
4. Search for a student by ID.
5. Update a student's email address.
6. Delete a record using a validated ID.
7. Handle duplicate email constraint errors.
8. Perform multiple related operations inside a transaction.
9. Insert several records using batch processing.
10. Experiment with connection pooling using a suitable DataSource implementation.

28. Quick Revision Checklist

• JDBC is Java's standard database connectivity API.
• Core JDBC packages include java.sql and javax.sql.
• A JDBC driver enables communication with a specific database.
• Connection represents a database connection.
• Statement and PreparedStatement execute SQL statements.
• ResultSet provides access to query results.
• CallableStatement calls stored procedures.
• CRUD means Create, Read, Update, and Delete.
• Try-with-resources helps close JDBC resources safely.
• Transactions use commit and rollback.
• Connection pooling enables connection reuse.
• Parameterized SQL and least-privilege accounts improve database security.

29. Official Learning Resources

• Java JDBC Basics Tutorial — learn about connections, statements, result sets, and transactions.
• JDBC Transactions Tutorial — understand commit, rollback, and transaction management.
• MySQL Connector/J Documentation — learn how to configure and use the MySQL JDBC driver.
• Advanced Java Training Course — explore the course details.
• Register for a Course Demo — register to explore the training program.
• JustAcademy Homepage — browse courses and learning resources.

30. Conclusion

Java Database Connectivity is a foundational technology for database-driven Java applications. It enables developers to establish database connections, execute SQL statements, retrieve records, perform CRUD operations, manage transactions, and handle database exceptions.

To build reliable Advanced Java applications, practice using PreparedStatement, managing resources with try-with-resources, handling transactions correctly, configuring database drivers, and applying secure database access practices.